package com.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resources;

import com.generate.CodeFactory;
import com.util.PropUtil;
import com.util.ResourceUtil;

public class DataBaseService {
	private Connection conn;
	private PreparedStatement ps;
	private String sql;
	private ResultSet rs;

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	public ResultSet getRs() {
		return rs;
	}

	public void setRs(ResultSet rs) {
		this.rs = rs;
	}

	public static boolean tryConnection(String url, String uName, String uPwd) {
		try {
			Class.forName(PropUtil.getDriverUrl());
			return DriverManager.getConnection(url, uName, uPwd) != null ? true
					: false;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return false;
	}

	public static Connection getConnection() {
		try {
			Class.forName(PropUtil.getDriverUrl());
			String url = PropUtil.getDbUrl();
			String uName = PropUtil.getUname();
			String uPwd = PropUtil.getUpwd();
			return DriverManager.getConnection(url, uName, uPwd);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<TableVo> getAllTableNames() {
		if (ResourceUtil.getConfigByName("dbtype").equals("mysql")) {
			this.sql = "select table_name,table_comment from information_schema.tables where table_schema = (select schema())";
		}

		if (ResourceUtil.getConfigByName("dbtype").equals("sqlserver")) {
			this.sql = "SELECT DISTINCT cast(d.name as varchar(100)) as table_name, isnull(cast(f.value as varchar(100)),'') as table_comment  FROM syscolumns a ";

			this.sql += " LEFT JOIN systypes b ON a.xusertype= b.xusertype";
			this.sql += " INNER JOIN sysobjects d ON a.id= d.id ";
			this.sql += " AND d.xtype= 'U' ";
			this.sql += " AND d.name<> 'dtproperties'";
			this.sql += " LEFT JOIN syscomments e ON a.cdefault= e.id";
			this.sql += " LEFT JOIN sys.extended_properties g ON a.id= G.major_id ";
			this.sql += " AND a.colid= g.minor_id";
			this.sql += " LEFT JOIN sys.extended_properties f ON d.id= f.major_id";
			this.sql += " AND f.minor_id= 0";
		}

		List<TableVo> ls = new ArrayList<TableVo>();
		System.out.println(this.sql);
		try {
			this.conn = getConnection();
			this.ps = this.conn.prepareStatement(this.sql);
			this.rs = this.ps.executeQuery();
			while (this.rs.next()) {
				TableVo vo = new TableVo();
				vo.setTableName(rs.getString(1));
				vo.setTableComment(rs.getString(2));
				ls.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (this.ps != null) {
					this.ps.close();
					this.ps = null;
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} finally {
			try {
				if (this.ps != null) {
					this.ps.close();
					this.ps = null;
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return ls;
	}

	public List<ColumnsVo> getColumnsByTableCode(String tableCode) {
		// 查询字段，列类型，注释

		String sql = "";

		if (ResourceUtil.getConfigByName("dbtype").equals("mysql")) {
			sql = "SELECT COLUMN_COMMENT NAME," + " DATA_TYPE TYPE,"
					+ " COLUMN_NAME CODE," + " IS_NULLABLE ISNULL,"
					+ " NUMERIC_SCALE NUMBERICSCALE,"
					+ " NUMERIC_PRECISION NUMBERLENGTH,"
					+ " CHARACTER_MAXIMUM_LENGTH STRLENGTH" + " FROM"
					+ " information_schema. COLUMNS" + " WHERE"
					+ " table_name = ?"
					+ " AND TABLE_SCHEMA = (SELECT DATABASE())" + " ORDER BY"
					+ " ordinal_position ASC";
		}
		if (ResourceUtil.getConfigByName("dbtype").equals("sqlserver")) {
			sql = "SELECT ";
			sql += " cast(isnull(g.[value], '') as varchar(100)) AS NAME,";
			sql += " (CASE WHEN system_type_id = 167 THEN 'VARCHAR' WHEN system_type_id=175 THEN 'CHAR'";
			sql += " WHEN system_type_id=56 THEN 'INT'";
			sql += " WHEN system_type_id=40 THEN 'DATE'";
			sql += " WHEN system_type_id=61 THEN 'DATETIME'";
			sql += " WHEN system_type_id=108 THEN 'DECIMAL' END )  AS TYPE,";
			sql += " cast(a.name as varchar(100)) AS CODE,";
			sql += " (CASE WHEN is_nullable = 1 THEN 'YES' ELSE 'NO' END )  AS ISNULL,";
			sql += " cast(scale as varchar(100)) NUMBERICSCALE,";
			sql += " cast(precision as varchar(100)) NUMBERLENGTH,";
			sql += " cast(max_length as varchar(100)) STRLENGTH";

			sql += " FROM";
			sql += " sys.columns a";
			sql += " LEFT JOIN sys.extended_properties g ON (";
			sql += " a.object_id = g.major_id";
			sql += " AND g.minor_id = a.column_id";
			sql += " )";
			sql += " WHERE";
			sql += " object_id = (";
			sql += " SELECT";
			sql += " object_id";
			sql += " FROM";
			sql += " sys.tables";
			sql += " WHERE";
			sql += " name = ?";
			sql += " )";
			sql += " AND g.[value] IS NOT NULL";
		}

		System.out.println(sql);
		
		// dev_biss_db 部署的时候，dev_biss_db 独立的服务器，不需要做这个条件的限制
		List<Map<String, Object>> ls = queryListBySql(sql, tableCode);

		List<ColumnsVo> lsvo = new ArrayList<ColumnsVo>();

		String columnType = "";

		for (Map<String, Object> map : ls) {
			columnType = map.get("TYPE").toString().toUpperCase();
			ColumnsVo vo = new ColumnsVo();
			vo.setCode(map.get("CODE").toString());
			vo.setName(map.get("NAME").toString());
			vo.setType(ColumnType.getTypeValue(columnType));
			vo.setComments(map.get("NAME") == null ? "" : map.get("NAME")
					.toString());
			vo.setIsNull(map.get("ISNULL").toString().equals("YES") ? "Y" : "N");
			if (ColumnType.isString(columnType)) {
				vo.setLength(Integer.valueOf(map.get("STRLENGTH").toString()));
			} else if (ColumnType.isInt(columnType)) {
				vo.setLength(Integer
						.valueOf(map.get("NUMBERLENGTH").toString()));
				vo.setPointLength(Integer.valueOf(map.get("NUMBERICSCALE")
						.toString()));
			}

			lsvo.add(vo);
		}

		return lsvo;

	}

	private List<Map<String, Object>> queryListBySql(String sql,
			String... params) {

		List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();
		try {
			this.conn = getConnection();
			this.ps = this.conn.prepareStatement(sql);

			int j = 1;
			for (String param : params) {
				ps.setString(j++, param);
			}

			this.rs = this.ps.executeQuery();
			while (this.rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for (int i = 1; i <= count; i++) {
					map.put(rsmd.getColumnName(i),
							rs.getString(rsmd.getColumnName(i)));
				}
				ls.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (this.ps != null) {
					this.ps.close();
					this.ps = null;
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} finally {
			try {
				if (this.ps != null) {
					this.ps.close();
					this.ps = null;
				}
				if (this.conn != null) {
					this.conn.close();
					this.conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return ls;
	}

	/** 数据库和Java类型对照 **/
	public static enum ColumnType {
		TINYINT("Integer"), SMALLINT("Integer"), MEDIUMINT("Integer"), INT(
				"Long"), INTS_UNSINGED("Long"), BIGINT("BigInteger"), VARCHAR(
				"String"), CHAR("String"), TIME("time"), DATE("Date"), DATETIME(
				"Date"), TIMESTAMP("Timestamp"), DECIMAL("BigDecimal"), FLOAT(
				"Float"), DOUBLE("Double");

		private String type;

		ColumnType(String type) {
			this.type = type;
		}

		public static boolean isInt(String type) {
			if (type.equals(ColumnType.TINYINT.toString())
					|| type.equals(ColumnType.SMALLINT.toString())
					|| type.equals(ColumnType.MEDIUMINT.toString())
					|| type.equals(ColumnType.INT.toString())
					|| type.equals(ColumnType.INTS_UNSINGED.toString())
					|| type.equals(ColumnType.BIGINT.toString())
					|| type.equals(ColumnType.DECIMAL.toString())
					|| type.equals(ColumnType.FLOAT.toString())
					|| type.equals(ColumnType.DOUBLE.toString())) {
				return true;
			}
			return false;
		}

		public static boolean isString(String type) {
			if (type.equals(ColumnType.VARCHAR.toString())
					|| type.equals(ColumnType.CHAR.toString())) {
				return true;
			}
			return false;
		}

		public static boolean isDate(String type) {
			if (type.equals(ColumnType.DATETIME.toString())
					|| type.equals(ColumnType.DATE.toString())
					|| type.equals(ColumnType.TIME.toString())
					|| type.equals(ColumnType.TIMESTAMP.toString())) {
				return true;
			}
			return false;
		}

		public String getValue() {
			return this.type;
		}

		public static String getTypeValue(String type) {
			for (ColumnType c : ColumnType.values()) {
				if (type.toUpperCase().equals(c.toString())) {
					return c.getValue();
				}
			}
			return "";
		}
	}

	public static void main(String[] args) {
		List<ColumnsVo> ls_column = new DataBaseService()
				.getColumnsByTableCode("test2".toUpperCase());
		System.out.println(ls_column.size());
	}
}
